package com.etc.utils;

import com.etc.entity.sensor.BizSensorConfig;
import com.etc.entity.warning.BizThreshold;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author : DDS
 * @Description : poiExcel工具类
 * @Date: 2019-12-07 11:01
 */
public class PoiExcelUtil {

    /**
     * 查询指定目录中电子表格中所有的数据
     *
     * @param
     * @return
     */
    public static List<BizSensorConfig> toLead(String fileName, InputStream inputStream) throws IOException {
        boolean notNull = false;
        List<BizSensorConfig> list = new ArrayList<>();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }

        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }

        for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
            BizSensorConfig bizSensorConfig = new BizSensorConfig();
            Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
            if (row == null && row.equals("")) {
                continue;
            }

            if (StringUtil.isNotNull(row.getCell(3).getStringCellValue())) {
                bizSensorConfig.setSort(Integer.valueOf(row.getCell(3).getStringCellValue()));
            } else {
                bizSensorConfig.setSort(null);
            }
            if (StringUtil.isNotNull(row.getCell(7).getStringCellValue())) {
                bizSensorConfig.setBandwidth(Integer.valueOf(row.getCell(7).getStringCellValue()));
            } else {
                bizSensorConfig.setBandwidth(null);
            }
            if (StringUtil.isNotNull(row.getCell(6).getStringCellValue())) {
                bizSensorConfig.setCentreWavelength(Float.valueOf(row.getCell(6).getStringCellValue()));
            } else {
                bizSensorConfig.setCentreWavelength(null);
            }
            if (StringUtil.isNotNull(row.getCell(8).getStringCellValue())) {
                bizSensorConfig.setSensorCoefficient(Float.valueOf(row.getCell(8).getStringCellValue()));
            } else {
                bizSensorConfig.setSensorCoefficient(null);
            }
            if (StringUtil.isNotNull(row.getCell(10).getStringCellValue())) {
                bizSensorConfig.setOffsetCoefficient(Float.valueOf(row.getCell(10).getStringCellValue()));
            } else {
                bizSensorConfig.setOffsetCoefficient(null);
            }

            bizSensorConfig.setIndexType(row.getCell(0).getStringCellValue());
            bizSensorConfig.setSensorPosition(row.getCell(1).getStringCellValue());
            bizSensorConfig.setBridgeSpan(row.getCell(2).getStringCellValue());
            bizSensorConfig.setSensorNumber(row.getCell(4).getStringCellValue());
            bizSensorConfig.setType(row.getCell(5).getStringCellValue());
            bizSensorConfig.setOffsetSensorNumber(row.getCell(9).getStringCellValue());
            bizSensorConfig.setFormula(row.getCell(11).getStringCellValue());

            list.add(bizSensorConfig);
        }
        return list;
    }

    /**
     * 查询指定目录中电子表格中所有的数据
     *
     * @param
     * @return
     */
    public static List<BizThreshold> toLead_threshold(String fileName, InputStream inputStream) throws IOException {
        boolean notNull = false;
        List<BizThreshold> list = new ArrayList<>();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }

        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }

        for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
            BizThreshold bizThreshold = new BizThreshold();
            Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
            if (row == null && row.equals("")) {
                continue;
            }

            if (StringUtil.isNotNull(row.getCell(5).getStringCellValue())){
                bizThreshold.setLevel(Integer.valueOf(row.getCell(5).getStringCellValue()));
            }else {
                bizThreshold.setLevel(null);
            }

            if (StringUtil.isNotNull(row.getCell(8).getStringCellValue())){
                bizThreshold.setRatio(Float.valueOf(row.getCell(8).getStringCellValue()));
            }
            if (StringUtil.isNotNull(row.getCell(10).getStringCellValue())){
                bizThreshold.setScopeFlag(Float.valueOf(row.getCell(5).getStringCellValue()));
            }

            bizThreshold.setId(row.getCell(0).getStringCellValue());
            bizThreshold.setCreateBy(row.getCell(1).getStringCellValue());
            bizThreshold.setUpdateBy(row.getCell(2).getStringCellValue());
            bizThreshold.setRemarks(row.getCell(3).getStringCellValue());
            bizThreshold.setDelFlag(row.getCell(4).getStringCellValue());
            bizThreshold.setValue(row.getCell(6).getStringCellValue());
            bizThreshold.setType(row.getCell(7).getStringCellValue());
            bizThreshold.setSensorPosition(row.getCell(9).getStringCellValue());

            list.add(bizThreshold);
        }
        return list;
    }

}
